package logic.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import logic.bean.MyNoteInfo;
import logic.db.DBHelper;

import org.geometerplus.android.util.SQLiteUtil;
import org.geometerplus.fbreader.book.Bookmark;
import org.geometerplus.fbreader.book.BookmarkQuery;
import org.geometerplus.zlibrary.text.view.ZLTextFixedPosition;
import org.geometerplus.zlibrary.text.view.ZLTextPosition;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;


/**
 * 书签的数据访问对象
 * 
 * @author zouqp
 *         2014-10-11
 */

public class BookMarksDao
{
	private static BookMarksDao  dao;
	private final SQLiteDatabase myDatabase;
	
	private BookMarksDao(Context context) {
		DBHelper dbHelper = DBHelper.getInstance(context);
		myDatabase = dbHelper.getSqliteDatabaseW();
	}
	
	public static BookMarksDao getInstance(Context context) {
		dao = new BookMarksDao(context);
		return dao;
	}
	
	public boolean hasVisibleBookmark(long bookId) {
		final Cursor cursor = myDatabase.rawQuery("SELECT bookmark_id FROM Bookmarks WHERE book_id = " + bookId + " AND visible = 1 and type_id != 4  LIMIT 1", null);
		final boolean result = cursor.moveToNext();
		cursor.close();
		return result;
	}
	
	public List<Bookmark> loadBookmarks(BookmarkQuery query) {
		final LinkedList<Bookmark> list = new LinkedList<Bookmark>();
		final StringBuilder sql = new StringBuilder("SELECT").append(" bm.bookmark_id,bm.book_id,b.book_name,bm.bookmark_text,")
		                                                     .append("bm.creation_time,bm.modification_time,bm.access_time,bm.access_counter,")
		                                                     .append("bm.model_id,bm.paragraph,bm.word,bm.char,")
		                                                     .append("bm.end_paragraph,bm.end_word,bm.end_character,")
		                                                     .append("bm.style_id,bm.booknote_text,bm.content_id,bm.chapter_id, bm.type_id")
		                                                     .append(" FROM Bookmarks AS bm")
		                                                     .append(" INNER JOIN Books AS b ON b.id = bm.book_id")
		                                                     .append(" WHERE");
		if (query.Book != null)
		{
			sql.append(" b.id = " + query.Book.getId() + " AND");
		}
		sql.append(" bm.type_id != 4 and");
		sql.append(" bm.visible = " + (query.Visible ? 1 : 0)).append(" ORDER BY bm.bookmark_id").append(" LIMIT " + query.Limit * query.Page + "," + query.Limit);
		Cursor cursor = null;
		try
		{
			cursor = myDatabase.rawQuery(sql.toString(), null);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		while (cursor.moveToNext())
		{
			list.add(createBookmark(
			                        cursor.getLong(0),
			                        cursor.getLong(1),
			                        cursor.getString(2),
			                        cursor.getString(3),
			                        SQLiteUtil.getDate(cursor, 4),
			                        SQLiteUtil.getDate(cursor, 5),
			                        SQLiteUtil.getDate(cursor, 6),
			                        (int) cursor.getLong(7),
			                        cursor.getString(8),
			                        (int) cursor.getLong(9),
			                        (int) cursor.getLong(10),
			                        (int) cursor.getLong(11),
			                        (int) cursor.getLong(12),
			                        cursor.isNull(13) ? -1 : (int) cursor.getLong(13),
			                        cursor.isNull(14) ? -1 : (int) cursor.getLong(14),
			                        query.Visible,
			                        (int) cursor.getLong(15),
			                        cursor.getString(16),
			                        cursor.getString(17),
			                        cursor.getString(18),
			                        cursor.getInt(19)));
		}
		cursor.close();
		return list;
	}
	
	private SQLiteStatement myInsertBookmarkStatement;
	private SQLiteStatement myUpdateBookmarkStatement;
	
	public long saveBookmark(Bookmark bookmark) {
		SQLiteStatement statement;
		if (bookmark.getId() == -1)
		{
			if (myInsertBookmarkStatement == null)
			{
				myInsertBookmarkStatement = myDatabase.compileStatement("INSERT OR IGNORE INTO Bookmarks (book_id,bookmark_text,creation_time,modification_time,access_time,access_counter,model_id,paragraph,word,char,end_paragraph,end_word,end_character,visible,style_id,booknote_text,content_id, chapter_id, type_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			}
			statement = myInsertBookmarkStatement;
		}
		else
		{
			if (myUpdateBookmarkStatement == null)
			{
				myUpdateBookmarkStatement = myDatabase.compileStatement("UPDATE Bookmarks SET book_id = ?, bookmark_text = ?, creation_time =?, modification_time = ?,access_time = ?, access_counter = ?, model_id = ?, paragraph = ?, word = ?, char = ?, end_paragraph = ?, end_word = ?, end_character = ?, visible = ?, style_id = ?, booknote_text = ?, content_id = ?, chapter_id= ?, type_id = ? WHERE bookmark_id = ?");
			}
			statement = myUpdateBookmarkStatement;
		}
		
		statement.bindLong(1, bookmark.getBookId());
		statement.bindString(2, bookmark.getText());
		SQLiteUtil.bindDate(statement, 3, bookmark.getDate(Bookmark.DateType.Creation));
		SQLiteUtil.bindDate(statement, 4, bookmark.getDate(Bookmark.DateType.Modification));
		SQLiteUtil.bindDate(statement, 5, bookmark.getDate(Bookmark.DateType.Access));
		statement.bindLong(6, bookmark.getAccessCount());
		SQLiteUtil.bindString(statement, 7, bookmark.ModelId);
		statement.bindLong(8, bookmark.ParagraphIndex);
		statement.bindLong(9, bookmark.ElementIndex);
		statement.bindLong(10, bookmark.CharIndex);
		final ZLTextPosition end = bookmark.getEnd();
		if (end != null)
		{
			statement.bindLong(11, end.getParagraphIndex());
			statement.bindLong(12, end.getElementIndex());
			statement.bindLong(13, end.getCharIndex());
		}
		else
		{
			statement.bindLong(11, bookmark.getLength());
			statement.bindNull(12);
			statement.bindNull(13);
		}
		statement.bindLong(14, bookmark.IsVisible ? 1 : 0);
		statement.bindLong(15, bookmark.getStyleId());
		SQLiteUtil.bindString(statement, 16, bookmark.getBookNote());
		SQLiteUtil.bindString(statement, 17, bookmark.getContentID());
		SQLiteUtil.bindString(statement, 18, bookmark.getChapterID());
		statement.bindLong(19, bookmark.getMyTypeID());
		
		if (bookmark.getId() == -1)
		{
			return statement.executeInsert();
		}
		else
		{
			final long id = bookmark.getId();
			statement.bindLong(20, id);
			statement.execute();
			return id;
		}
	}
	
	private SQLiteStatement myDeleteBookmarkStatement;
	
	public void deleteBookmark(Bookmark bookmark) {
		if (myDeleteBookmarkStatement == null)
		{
			myDeleteBookmarkStatement = myDatabase.compileStatement("DELETE FROM Bookmarks WHERE bookmark_id = ?");
		}
		myDeleteBookmarkStatement.bindLong(1, bookmark.getId());
		myDeleteBookmarkStatement.execute();
	}
	
	public void deleteBook(long bookId, String account) {
		if (myDeleteBookmarkStatement == null)
		{
			myDeleteBookmarkStatement = myDatabase.compileStatement("DELETE FROM Bookmarks WHERE content_id = ?");
		}
		myDeleteBookmarkStatement.bindLong(1, bookId);
		myDeleteBookmarkStatement.execute();
	}
	
	public List<Bookmark> loadOneBookNotes(String bookDB_Id, String contentId, String title) {
		final ArrayList<Bookmark> list = new ArrayList<Bookmark>();
		final String sql = "SELECT * FROM Bookmarks WHERE content_id = ? AND book_id = ? AND type_id != 4  AND visible = 1 ORDER BY creation_time desc";
		String[] selectArgs = new String[] {
		        contentId,
		        bookDB_Id
		};
		
		final Cursor cursor = myDatabase.rawQuery(sql, selectArgs);
		while (cursor.moveToNext())
		{
			Bookmark bookmark = createBookmark(
			                                   cursor.getLong(0),
			                                   cursor.getLong(1),
			                                   title,
			                                   cursor.getString(2),
			                                   new Date(cursor.getLong(3)),
			                                   new Date(cursor.getLong(4)),
			                                   new Date(System.currentTimeMillis()),
			                                   1,
			                                   cursor.getString(10),
			                                   (int) cursor.getLong(7),
			                                   (int) cursor.getLong(8),
			                                   (int) cursor.getLong(9),
			                                   cursor.getInt(12),
			                                   cursor.getInt(13),
			                                   cursor.getInt(14),
			                                   cursor.getInt(11) == 1 ? true : false,
			                                   cursor.getInt(15),
			                                   cursor.getString(16),
			                                   contentId,
			                                   cursor.getString(18),
			                                   cursor.getInt(19));
			list.add(bookmark);
		}
		
		cursor.close();
		return list;
	}
	
	public ZLTextPosition getStoredPosition(long bookId, String chapterId) {
		ZLTextPosition position = null;
		Cursor cursor = myDatabase.rawQuery("SELECT paragraph,word,char FROM Bookmarks WHERE type_id = 4 and book_id = " + bookId + " and chapter_id = " + chapterId, null);
		if (cursor.moveToNext())
		{
			position = new ZLTextFixedPosition((int) cursor.getLong(0), (int) cursor.getLong(1), (int) cursor.getLong(2));
		}
		cursor.close();
		return position;
	}
	
	private SQLiteStatement myDeletePositionStatement;
	
	public void deletePosition(long bookId, String chapterId) {
		if (myDeletePositionStatement == null)
		{
			try
			{
				myDeletePositionStatement = myDatabase.compileStatement("DELETE FROM Bookmarks WHERE type_id = 4 and book_id = ? and chapter_id = ?");
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		myDeletePositionStatement.bindLong(1, bookId);
		myDeletePositionStatement.bindString(2, chapterId);
		myDeletePositionStatement.execute();
	}
	
	private SQLiteStatement myStorePositionStatement;
	
	public void storePosition(long bookId, String chapterId, ZLTextPosition position) {
		deletePosition(bookId, chapterId);
		if (myStorePositionStatement == null)
		{
			myStorePositionStatement = myDatabase.compileStatement("INSERT INTO Bookmarks(book_id,paragraph,word,char,chapter_id, creation_time, type_id) VALUES (?,?,?,?,?,?,?)");
		}
		myStorePositionStatement.bindLong(1, bookId);
		myStorePositionStatement.bindLong(2, position.getParagraphIndex());
		myStorePositionStatement.bindLong(3, position.getElementIndex());
		myStorePositionStatement.bindLong(4, position.getCharIndex());
		myStorePositionStatement.bindString(5, chapterId);
		myStorePositionStatement.bindLong(6, new Date().getTime());
		myStorePositionStatement.bindLong(7, 4);
		myStorePositionStatement.execute();
	}
	
	private Bookmark createBookmark(long id, long bookId, String bookTitle, String text, Date creationDate, Date modificationDate, Date accessDate, int accessCounter, String modelId, int start_paragraphIndex, int start_wordIndex, int start_charIndex, int end_paragraphIndex, int end_wordIndex, int end_charIndex, boolean isVisible, int styleId, String bookNote, String contentId, String chapterId, int typeId) {
		return new Bookmark(id, bookId, bookTitle, text, creationDate, modificationDate, accessDate, accessCounter, modelId, start_paragraphIndex, start_wordIndex, start_charIndex, end_paragraphIndex, end_wordIndex, end_charIndex, isVisible, styleId, bookNote, contentId, chapterId, typeId);
	}
	
	public List<MyNoteInfo> loadMyBookNotes(String account) {
		
		final ArrayList<MyNoteInfo> list = new ArrayList<MyNoteInfo>();
		Cursor cursor = null;
		try
		{
			final String sql = "select bm.book_id, bm.content_id, count(bm.bookmark_id) from Bookmarks as bm "
					+ "WHERE EXISTS (select b.id from Books as b  where b.account = ? and bm.book_id = b.id "
					+ "AND bm.style_id != ? AND bm.visible = ?) GROUP BY bm.book_id ORDER BY bm.creation_time DESC";
			cursor = myDatabase.rawQuery(sql, new String[] {
			        account,
			        "1",
			        "1"
			});
			while (cursor.moveToNext())
			{
				if(cursor.getInt(2)==0) continue;
				MyNoteInfo info = new MyNoteInfo(cursor.getLong(0), cursor.getString(1), cursor.getInt(2));
				list.add(info);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.err.println(e.toString());
		}
		finally
		{
			if (null != cursor) cursor.close();
		}
		
		return list;
	}
	
	public String getBookDbId(String contentId, String account) {
		String dbId = "1";
		final String sql = "SELECT book_id FROM bookmarks WHERE content_id = ? AND account = ? ";
		final Cursor cursor = myDatabase.rawQuery(sql, new String[] {
			contentId,
			account
		});
		if (cursor.moveToNext())
		{
			dbId = cursor.getString(0);
		}
		cursor.close();
		return dbId;
	}
	
}
